﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class AddressBookManager:ConnectionManager
    {

        public double GetWithHoldingTaxRate(string sBPCardCode)
        {
            try
            {

                SqlCommand oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;

                oCommand.CommandText = "SELECT WithHoldingTax FROM ADDRESSBOOK WHERE BPCode=@CardCode";
                oCommand.Parameters.Add(new SqlParameter("@CardCode", sBPCardCode));

                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return Convert.ToDouble (ds.Tables[0].Rows[0][0]);
            }
            catch
            {
                return 0;
            }
        }
        
        public string GetDivision(string sBPCardCode)
        {
            try
            {

                SqlCommand oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;

                oCommand.CommandText = "SELECT DIVISION FROM ADDRESSBOOK WHERE BPCode=@CardCode";
                oCommand.Parameters.Add(new SqlParameter("@CardCode", sBPCardCode));

                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0].Rows[0][0].ToString();
            }
            catch
            {
                return null;
            }
        }

        public string GetDocument(string sBPCardCode)
        {
            try
            {

                SqlCommand oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;

                oCommand.CommandText = "SELECT Documnt FROM ADDRESSBOOK WHERE BPCode=@CardCode";
                oCommand.Parameters.Add(new SqlParameter("@CardCode", sBPCardCode));

                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0].Rows[0][0].ToString();
            }
            catch
            {
                return null;
            }
        }

        public string GetPaymentTerm(string sBPCardCode)
        {
            try
            {

                SqlCommand oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;

                oCommand.CommandText = "SELECT PAYMENTTERM FROM ADDRESSBOOK WHERE BPCode=@CardCode";
                oCommand.Parameters.Add(new SqlParameter("@CardCode", sBPCardCode));

                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0].Rows[0][0].ToString();
            }
            catch
            {
                return null;
            }
        }

        public string GetPriceListCode(string sBPCardCode)
        {
            try
            {

                SqlCommand oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;
             
                oCommand.CommandText = "SELECT PRICELIST FROM ADDRESSBOOK WHERE BPCode=@CardCode";
                oCommand.Parameters.Add(new SqlParameter("@CardCode", sBPCardCode));
             
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0].Rows[0][0].ToString();
            }
            catch
            {
                return null;
            }
        }

        public DataTable GetGroupAddressBook(bool IsCustomer)
        {
            try
            {

                SqlCommand oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;
                if (IsCustomer == true)
                {
                    oCommand.CommandText = "SELECT * FROM CUSTOMER_GROUP";
                }
                else
                {
                    oCommand.CommandText = "SELECT * FROM VENDOR_GROUP";
                }

                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
        }

        public DataTable GetAddressBook(bool IsCustomer)
        {
            try
            {

                SqlCommand oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;
                if (IsCustomer == true)
                {
                    oCommand.CommandText = "SELECT *,(SELECT ACCOUNTS.AcctName FROM ACCOUNTS WHERE ACCOUNTS.AcctCode = SMCODE ) as AgentName FROM ADDRESSBOOK WHERE BPTYPE='CUSTOMER'";
                }
                else
                {
                    oCommand.CommandText = "SELECT *,(SELECT ACCOUNTS.AcctName FROM ACCOUNTS WHERE ACCOUNTS.AcctCode = SMCODE ) as AgentName FROM ADDRESSBOOK WHERE BPTYPE='VENDOR'";
                }

                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
        }

        public bool IsRecordExists(string sCode)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT * FROM ADDRESSBOOK WHERE BPCode = @BPCode";

                _COMMAND.Parameters.Add(new SqlParameter("@BPCode", sCode));

                DataSet ds = new DataSet();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oAdapter.SelectCommand = _COMMAND;
                oAdapter.Fill(ds);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #region Adding Card
        public SqlException AddPartnerCard(AddressBookUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {


                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;
                oCommand.CommandText = "INSERT INTO ADDRESSBOOK (BPCode, BPName, BPType, UnitNo, Street, Brgy, CityOrMunicipal, PostalCode, ProvinceOrMetro, Phone, Fax, ContactPerson, TIN, CreditLimit, PaymentTerm, PriceList, VATCode, WithHoldingTax, SMCODE, AREACODE, TERITORRY, CUSTOMERGROUP, BPCODEPARENT, Institution, StoreCode, Division, Disc1, Disc2, Disc3, Disc4, Documnt) VALUES (@BPCode, @BPName, @BPType, @UnitNo, @Street, @Brgy, @CityOrMunicipal, @PostalCode, @ProvinceOrMetro, @Phone, @Fax, @ContactPerson, @TIN, @CreditLimit, @PaymentTerm, @PriceList, @VATCode, @WithHoldingTax, @SMCODE, @AREACODE, @TERITORY, @CUSTOMERGROUP, @BPCodeParent, @Institution, @StoreCode, @Division, @Disc1, @Disc2, @Disc3, @Disc4, @Document)";

                oCommand.Parameters.Add(new SqlParameter("@BPCode", oUnit.Code));
                oCommand.Parameters.Add(new SqlParameter("@BPName", oUnit.Name));
                oCommand.Parameters.Add(new SqlParameter("@BPType", oUnit.Type));
                oCommand.Parameters.Add(new SqlParameter("@UnitNo", oUnit.UnitNo));
                oCommand.Parameters.Add(new SqlParameter("@Street", oUnit.Street));
                oCommand.Parameters.Add(new SqlParameter("@Brgy", oUnit.Barangay));
                oCommand.Parameters.Add(new SqlParameter("@CityOrMunicipal", oUnit.CityOrMunicipality));
                oCommand.Parameters.Add(new SqlParameter("@PostalCode", oUnit.PostalCode));
                oCommand.Parameters.Add(new SqlParameter("@ProvinceOrMetro", oUnit.ProvinceOrMetro));
                oCommand.Parameters.Add(new SqlParameter("@Phone", oUnit.Phone));
                oCommand.Parameters.Add(new SqlParameter("@Fax", oUnit.Fax));
                oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
                oCommand.Parameters.Add(new SqlParameter("@TIN", oUnit.TIN));
                oCommand.Parameters.Add(new SqlParameter("@CreditLimit", oUnit.CreditLimit));
                oCommand.Parameters.Add(new SqlParameter("@PaymentTerm", oUnit.PaymentTerm));
                oCommand.Parameters.Add(new SqlParameter("@PriceList", oUnit.PriceList));
                oCommand.Parameters.Add(new SqlParameter("@VATCode", oUnit.VATCode));
                oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
                oCommand.Parameters.Add(new SqlParameter("@SMCODE", oUnit.BuyerSalesMan));
                oCommand.Parameters.Add(new SqlParameter("@AREACODE", oUnit.AreaCode));
                oCommand.Parameters.Add(new SqlParameter("@TERITORY", oUnit.Teritorry));
                oCommand.Parameters.Add(new SqlParameter("@CUSTOMERGROUP", oUnit.CustomerGroup));
                oCommand.Parameters.Add(new SqlParameter("@BPCodeParent", oUnit.ParentCode));
                oCommand.Parameters.Add(new SqlParameter("@Institution", oUnit.Institution));
                oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
                oCommand.Parameters.Add(new SqlParameter("@Division", oUnit.Division));
                oCommand.Parameters.Add(new SqlParameter("@Disc1", oUnit.Discount1));
                oCommand.Parameters.Add(new SqlParameter("@Disc2", oUnit.Discount2));
                oCommand.Parameters.Add(new SqlParameter("@Disc3", oUnit.Discount3));
                oCommand.Parameters.Add(new SqlParameter("@Disc4", oUnit.Discount4));
                oCommand.Parameters.Add(new SqlParameter("@Document", oUnit.Document));

                oCommand.ExecuteNonQuery();

                return null;

            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdatePartner(AddressBookUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {


                oCommand.Connection = this.Connection;
                oCommand.CommandType = CommandType.Text;

                oCommand.CommandText = "UPDATE ADDRESSBOOK " +
                                        "SET BPName=@BPName, " +
                                            "BPType=@BPType,  " +
                                            "UnitNo=@UnitNo, " +
                                            "Street=@Street, " +
                                            "Brgy=@Brgy, " +
                                            "CityOrMunicipal=@CityOrMunicipal, " +
                                            "PostalCode=@PostalCode, " +
                                            "ProvinceOrMetro=@ProvinceOrMetro, " +
                                            "Phone=@Phone, " +
                                            "Fax=@Fax, " +
                                            "ContactPerson=@ContactPerson, " +
                                            "TIN=@TIN, " +
                                            "PaymentTerm=@PaymentTerm, " +
                                            "CreditLimit=@CreditLimit, " +
                                            "PriceList=@PriceList, " +
                                            "SMCODE=@SMCODE, " +
                                            "AREACODE=@AREACODE, " +
                                            "TERITORRY=@TERITORRY, " +
                                            "CUSTOMERGROUP=@CUSTOMERGROUP, " +
                                            "BPCODEPARENT=@BPCODEPARENT, " +
                                            "Institution=@Institution, " +
                                            "StoreCode=@StoreCode, " +
                                            "VATCode=@VATCode, " +
                                            "WithHoldingTax=@WithHoldingTax, " +
                                            "Division=@Division, " +
                                            "Disc1=@Disc1, " +
                                            "Disc2=@Disc2, " +
                                            "Disc3=@Disc3, " +
                                            "Disc4=@Disc4, " +
                                            "Documnt=@Document " +
                                         "WHERE BPCode = @BPCode";

                oCommand.Parameters.Add(new SqlParameter("@BPCode", oUnit.Code));
                oCommand.Parameters.Add(new SqlParameter("@BPName", oUnit.Name));
                oCommand.Parameters.Add(new SqlParameter("@BPType", oUnit.Type));

                oCommand.Parameters.Add(new SqlParameter("@UnitNo", oUnit.UnitNo));
                oCommand.Parameters.Add(new SqlParameter("@Street", oUnit.Street));
                oCommand.Parameters.Add(new SqlParameter("@Brgy", oUnit.Barangay));
                oCommand.Parameters.Add(new SqlParameter("@CityOrMunicipal", oUnit.CityOrMunicipality));
                oCommand.Parameters.Add(new SqlParameter("@PostalCode", oUnit.PostalCode));
                oCommand.Parameters.Add(new SqlParameter("@ProvinceOrMetro", oUnit.ProvinceOrMetro));

                oCommand.Parameters.Add(new SqlParameter("@Phone", oUnit.Phone));
                oCommand.Parameters.Add(new SqlParameter("@Fax", oUnit.Fax));
                oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));

                oCommand.Parameters.Add(new SqlParameter("@TIN", oUnit.TIN));
                oCommand.Parameters.Add(new SqlParameter("@PaymentTerm", oUnit.PaymentTerm));
                oCommand.Parameters.Add(new SqlParameter("@CreditLimit", oUnit.CreditLimit));
                oCommand.Parameters.Add(new SqlParameter("@PriceList", oUnit.PriceList));
                oCommand.Parameters.Add(new SqlParameter("@SMCODE", oUnit.BuyerSalesMan));
                oCommand.Parameters.Add(new SqlParameter("@AREACODE", oUnit.AreaCode));
                oCommand.Parameters.Add(new SqlParameter("@CUSTOMERGROUP", oUnit.CustomerGroup));
                oCommand.Parameters.Add(new SqlParameter("@TERITORRY", oUnit.Teritorry));
                oCommand.Parameters.Add(new SqlParameter("@BPCODEPARENT", oUnit.ParentCode));
                oCommand.Parameters.Add(new SqlParameter("@Institution", oUnit.Institution));
                oCommand.Parameters.Add(new SqlParameter("@VATCode", oUnit.VATCode));
                oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
                oCommand.Parameters.Add(new SqlParameter("@Division", oUnit.Division));
                oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
                oCommand.Parameters.Add(new SqlParameter("@Disc1", oUnit.Discount1));
                oCommand.Parameters.Add(new SqlParameter("@Disc2", oUnit.Discount2));
                oCommand.Parameters.Add(new SqlParameter("@Disc3", oUnit.Discount3));
                oCommand.Parameters.Add(new SqlParameter("@Disc4", oUnit.Discount4));
                oCommand.Parameters.Add(new SqlParameter("@Document", oUnit.Document));

                oCommand.ExecuteNonQuery();
                return null;

            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }
        #endregion

        #region Get Records
        public DataTable GetAllPartnersByBPType(string sBPType)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT BPCode, BPName FROM ADDRESSBOOK WHERE BPType=@BPType";

                _COMMAND.Parameters.Add(new SqlParameter("@BPType", sBPType));

                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable BrowseByBPCode(string sCode, string sType)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT TOP 300 ADDRESSBOOK.*, PriceList.PriceListName FROM ADDRESSBOOK LEFT JOIN PriceList ON ADDRESSBOOK.PRICELIST = PriceList.PRICELISTCODE WHERE BPType=@BPType AND BPCode LIKE @BPCode ORDER BY BPCODE"; 
                _COMMAND.Parameters.Add(new SqlParameter("@BPCode", sCode));
                _COMMAND.Parameters.Add(new SqlParameter("@BPType", sType));

                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable BrowseByBPName(string sName, string sType)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT TOP 300 ADDRESSBOOK.*, PRICELISTNAME  FROM ADDRESSBOOK LEFT JOIN PRICELIST ON ADDRESSBOOK.PRICELIST = PRICELIST.PRICELISTCODE WHERE BPType=@BPType AND BPName LIKE @BPName ORDER BY BPNAME";
                _COMMAND.Parameters.Add(new SqlParameter("@BPName", sName));
                _COMMAND.Parameters.Add(new SqlParameter("@BPType", sType));

                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable BrowseByStoreCode(string _STORECODE, string sType)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT TOP 300 ADDRESSBOOK.*, PRICELISTNAME  FROM ADDRESSBOOK LEFT JOIN PRICELIST ON ADDRESSBOOK.PRICELIST = PRICELIST.PRICELISTCODE WHERE BPType=@BPType AND StoreCode LIKE @StoreCode ORDER BY StoreCode";
                _COMMAND.Parameters.Add(new SqlParameter("@StoreCode", _STORECODE));
                _COMMAND.Parameters.Add(new SqlParameter("@BPType", sType));

                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetByStoreCode(string _CODE, string _TYPE)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT ADDRESSBOOK.*, PriceList.PriceListName FROM ADDRESSBOOK LEFT JOIN PriceList ON ADDRESSBOOK.PRICELIST = PriceList.PRICELISTCODE WHERE BPType=@BPType AND StoreCode=@StoreCode";
                _COMMAND.Parameters.Add(new SqlParameter("@StoreCode", _CODE));
                _COMMAND.Parameters.Add(new SqlParameter("@BPType", _TYPE));

                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetByBPCode(string _CODE, string _TYPE)
        {
            try
            {
                SqlCommand _COMMAND = new SqlCommand();
                SqlDataAdapter _ADAPTER = new SqlDataAdapter();
                DataSet ds = new DataSet();

                _COMMAND.Connection = this.Connection;
                _COMMAND.CommandType = CommandType.Text;
                _COMMAND.CommandText = "SELECT ADDRESSBOOK.*, PriceList.PriceListName FROM ADDRESSBOOK LEFT JOIN PriceList ON ADDRESSBOOK.PRICELIST = PriceList.PRICELISTCODE WHERE BPType=@BPType AND BPCode=@BPCode";
                _COMMAND.Parameters.Add(new SqlParameter("@BPCode", _CODE));
                _COMMAND.Parameters.Add(new SqlParameter("@BPType", _TYPE));

                _ADAPTER.SelectCommand = _COMMAND;

                _ADAPTER.Fill(ds);

                return ds.Tables[0];


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region Miscellaneous
        public DataTable GetAreaCodes()
        {
            SqlCommand oCommand = new SqlCommand()
            {
                Connection = this.Connection,
                CommandText = "SELECT * FROM AREACODES"
            };
            DataSet ds = new DataSet();

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }
        #endregion
    }
}
